/*
 *    GeoTools - The Open Source Java GIS Toolkit
 *    http://geotools.org
 *
 *    (C) 2002-2008, Open Source Geospatial Foundation (OSGeo)
 *
 *    This library is free software; you can redistribute it and/or
 *    modify it under the terms of the GNU Lesser General Public
 *    License as published by the Free Software Foundation;
 *    version 2.1 of the License.
 *
 *    This library is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *    Lesser General Public License for more details.
 */
package org.geotools.data.oracle;

import org.geotools.jdbc.JDBCPrimaryKeyTestSetup;

public class OraclePrimaryKeyTestSetup extends JDBCPrimaryKeyTestSetup {

    protected OraclePrimaryKeyTestSetup() {
        super(new OracleTestSetup());
    }

    @Override
    protected void createAutoGeneratedPrimaryKeyTable() throws Exception {}

    @Override
    protected void createSequencedPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE seq (key INT, name VARCHAR(255),"
                        + " geom MDSYS.SDO_GEOMETRY, PRIMARY KEY(key))");
        run(
                "INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )"
                        + " VALUES ('seq','geom',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), "
                        + "MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326)");
        run("CREATE SEQUENCE seq_key_sequence START WITH 1");

        run("INSERT INTO seq VALUES (seq_key_sequence.NEXTVAL,'one',NULL)");
        run("INSERT INTO seq VALUES (seq_key_sequence.NEXTVAL,'two',NULL)");
        run("INSERT INTO seq VALUES (seq_key_sequence.NEXTVAL,'three',NULL)");
    }

    @Override
    protected void createNonIncrementingPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE noninc (key INT, name VARCHAR(255),"
                        + " geom MDSYS.SDO_GEOMETRY, PRIMARY KEY(key))");
        run(
                "INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )"
                        + " VALUES ('noninc','geom',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), "
                        + "MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326)");
        run("INSERT INTO noninc VALUES (1,'one',NULL)");
        run("INSERT INTO noninc VALUES (2,'two',NULL)");
        run("INSERT INTO noninc VALUES (3,'three',NULL)");
    }

    @Override
    protected void createMultiColumnPrimaryKeyTable() throws Exception {
        run(
                "CREATE TABLE multi (key1 INT, key2 VARCHAR(255), name VARCHAR(255),"
                        + " geom MDSYS.SDO_GEOMETRY, PRIMARY KEY(key1,key2))");
        run(
                "INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )"
                        + " VALUES ('multi','geom',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), "
                        + "MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326)");
        run("INSERT INTO multi VALUES (1,'x','one',NULL)");
        run("INSERT INTO multi VALUES (2,'y','two',NULL)");
        run("INSERT INTO multi VALUES (3,'z','three',NULL)");
    }

    @Override
    protected void createNullPrimaryKeyTable() throws Exception {
        run("CREATE TABLE nokey (name VARCHAR(255))");
        run("INSERT INTO nokey VALUES ('one')");
        run("INSERT INTO nokey VALUES ('two')");
        run("INSERT INTO nokey VALUES ('three')");
    }

    @Override
    protected void createUniqueIndexTable() throws Exception {
        run("CREATE TABLE uniq (key INT, name VARCHAR(255)," + " geom MDSYS.SDO_GEOMETRY)");
        run(
                "INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )"
                        + " VALUES ('uniq','geom',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), "
                        + "MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326)");
        run("CREATE UNIQUE INDEX uniq_key_index ON uniq(key)");

        run("INSERT INTO uniq VALUES (1,'one',NULL)");
        run("INSERT INTO uniq VALUES (2,'two',NULL)");
        run("INSERT INTO uniq VALUES (3,'three',NULL)");
    }

    @Override
    protected void createNonFirstColumnPrimaryKey() throws Exception {
        run("CREATE TABLE nonfirst (name VARCHAR(255), key INT, " + " geom MDSYS.SDO_GEOMETRY)");
        run(
                "INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )"
                        + " VALUES ('nonfirst','geom',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.5), "
                        + "MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.5)), 4326)");
        run("CREATE UNIQUE INDEX nonfirst_key_index ON nonfirst(key)");

        run("INSERT INTO nonfirst VALUES ('one',1, NULL)");
        run("INSERT INTO nonfirst VALUES ('two',2, NULL)");
        run("INSERT INTO nonfirst VALUES ('three',3,NULL)");
    }

    @Override
    protected void dropAutoGeneratedPrimaryKeyTable() throws Exception {}

    @Override
    protected void dropSequencedPrimaryKeyTable() throws Exception {
        runSafe("DROP TABLE seq PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'SEQ'");
        runSafe("DROP SEQUENCE seq_key_sequence");
    }

    @Override
    protected void dropNonIncrementingPrimaryKeyTable() throws Exception {
        runSafe("DROP TABLE noninc PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'NONINC'");
    }

    @Override
    protected void dropMultiColumnPrimaryKeyTable() throws Exception {
        runSafe("DROP TABLE multi PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'MULTI'");
    }

    @Override
    protected void dropNullPrimaryKeyTable() throws Exception {
        runSafe("DROP TABLE nokey PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'NOKEY'");
    }

    @Override
    protected void dropUniqueIndexTable() throws Exception {
        runSafe("DROP INDEX uniq_key_index");
        runSafe("DROP TABLE uniq PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'UNIQ'");
    }

    @Override
    protected void dropNonFirstPrimaryKeyTable() throws Exception {
        runSafe("DROP INDEX nonfirst_key_index");
        runSafe("DROP TABLE nonfirst PURGE");
        runSafe("DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'NONFIRST'");
    }
}
